# coding: utf-8


class ExpressionType:
    query = 1
    update = 2
    delete = 3
    insert = 4


class Expression:
    """expression of farmer, render SQL by condition. eg:

        >>> exp = Expression(table='test_table',
                             condition_str_tuple=('id < 10', 'id > 5'),
                             condition_dict={'name': 'ida', 'status': 'normal'})
        >>> exp.render_sql(expression_type=ExpressionType.query)
        SELECT * FROM `test_table` where `name`=%s AND `status`=%s AND id < 10 AND id > 5
        >>> exp.values
        ('ida', 'normal')

        >>> exp = Expression(table='test_expression',
                             condition_str_tuple=('id <= 20',),
                             fields_dict={'name': 'tom', 'desc': 'tom name'})
        >>> exp.render_sql(expression_type=ExpressionType.update)
        UPDATE `test_expression` SET `name`=%s, `desc`=%s WHERE id <= 20
        >>> exp.values
        ('tom', 'tom name')

        >>> exp = Expression(table='test_expression',
                             fields_dict={'name': 'loli', 'desc': 'iloli'})
        >>> exp.render_sql(expression_type=ExpressionType.insert)
        INSERT INTO `test_expression` (`name`, `desc`) VALUES (%s, %s)
        >>> exp.values
        ('loli', 'iloli')

        >>> exp = Expression(table='test_expression',
                             condition_str_tuple=("name='tom'",))
        >>> exp.render_sql(expression_type=ExpressionType.delete)
        DELETE FROM test_expression where `name`=%s
        >>> exp.values
        ('tom',)

    """
    query_sql_template = "SELECT {selected_fields} FROM {table}"
    update_sql_template = "UPDATE {table} SET {fields_dict}"
    delete_sql_template = "DELETE FROM {table}"
    insert_sql_template = "INSERT INTO {table} ({fields}) VALUES ({values})"
    condition_sql_template = " WHERE {condition}"
    order_by_sql_template = " ORDER BY {field}"
    group_by_sql_template = " GROUP BY {field}"

    def __init__(self, table: str, condition_str_tuple=tuple(), condition_dict=None, fields_dict=None):
        """init expression when query

        :param table: which database table fetch data from
        :param condition_str_tuple: condition by string, eg: ('id < 10', 'id > 5') ==> `id < 10 AND id > 5`
        :param condition_dict: query condition, with field name and value
        :param fields_dict: fields name and value, for update or insert
        """
        self.condition_str_tuple = condition_str_tuple
        self.condition_dict = condition_dict or {}
        self.table = table
        self.fields_dict = fields_dict or {}
        self.condition_items = self.condition_dict.items()
        self.fields_items = self.fields_dict.items()
        self.init_setup()

    def init_setup(self):
        self._values = tuple()
        self._order_by = ""
        self._group_by = ""
        self._selected_field = "*"
        self._value_in_field_list = []
        self._value_in_values_list = []
        self._limits = ""

    @property
    def values(self):
        ret = getattr(self, '_values', tuple())
        if self._value_in_values_list:
            for _value_in_values in self._value_in_values_list:
                ret += _value_in_values
        return ret

    @values.setter
    def values(self, values_tuple):
        self._values = values_tuple

    def render_sql(self, expression_type=ExpressionType.query) -> str:
        condition_ret = self._build_condition()
        if expression_type == ExpressionType.query:
            ret = self.query_sql_template.format(selected_fields=self._selected_field, table=self.table)
            self.values = tuple(v for _, v in self.condition_items)
        elif expression_type == ExpressionType.update:
            ret = self.update_sql_template.format(
                table=self.table,
                fields_dict=', '.join(["`%s`=%%s" % item for item, _ in self.fields_items])
            )
            _values = tuple(v for _, v in self.fields_items)
            _values += tuple(v for _, v in self.condition_items)
            self.values = _values
        elif expression_type == ExpressionType.insert:
            ret = self.insert_sql_template.format(
                table=self.table,
                fields=', '.join(map(lambda x: '`%s`' % x, self.fields_dict.keys())),
                values=', '.join(['%s' for _ in self.fields_dict.values()])
            )
            self.values = tuple(v for _, v in self.fields_items)
        elif expression_type == ExpressionType.delete:
            ret = self.delete_sql_template.format(table=self.table)
            self.values = tuple(v for _, v in self.condition_items)
        else:
            raise ValueError("Invalid expression type")

        ret += condition_ret
        ret += self._group_by
        ret += self._order_by
        ret += self._limits
        return ret

    def _build_condition(self) -> str:
        condition_dict_ret, condition_str_ret = '', ''
        if not any([self.condition_dict, self.condition_str_tuple, self._value_in_field_list]):
            return ''

        if self.condition_dict:
            condition_dict_ret = ' AND '.join(["`%s`=%%s" % item for item, _ in self.condition_items])

        if self.condition_str_tuple:
            condition_str_ret += ' AND '.join(self.condition_str_tuple)

        if self._value_in_field_list:
            condition_str_ret += ' AND '.join([
                '`%s` IN (%s)' % (
                    _field, ', '.join(['%s' for _ in _values])
                ) for _field, _values in zip(self._value_in_field_list, self._value_in_values_list)
            ])

        if condition_dict_ret and condition_str_ret:
            condition_ret = ' AND '.join([condition_dict_ret, condition_str_ret])
        else:
            condition_ret = condition_dict_ret or condition_str_ret

        return self.condition_sql_template.format(condition=condition_ret)

    def update_condition_str_tuple(self, condition_str_tuple: tuple):
        if not condition_str_tuple:
            return
        self.condition_str_tuple += condition_str_tuple

    def update_condition_dict(self, condition_dict: dict):
        if not condition_dict:
            return
        self.condition_dict.update(condition_dict)

    def update_fields_dict(self, fields_dict):
        if not fields_dict:
            return
        self.fields_dict.update(fields_dict)

    def order_by(self, field: str):
        self._order_by = self.order_by_sql_template.format(field=field)

    def group_by(self, field: str):
        self._selected_field = field
        self._group_by = self.group_by_sql_template.format(field=field)

    def distinct_by(self, field: str):
        self._selected_field = "DISTINCT({field})".format(field=field)

    def value_in(self, field: str, values: tuple):
        self._value_in_field_list.append(field)
        self._value_in_values_list.append(tuple(values))

    def limits(self, limit: int, offset: int=0):
        if not isinstance(limit, int) or not isinstance(offset, int):
            raise ValueError("LIMITS type error, must bu `int`")

        self._limits = " LIMIT %s" % limit
        if offset:
            self._limits += ', %s' % offset
